/*
	This proc gives the close lots information and there corresponding TEST yield.
	EXEC SP_TEST_SOFT_BIN_YIELD_REPORT '0','0','0','0','0','0','XLREPORT', '0'
	EXEC SP_TEST_SOFT_BIN_YIELD_REPORT '0','0','0','0','10/01/2010','10/31/2010','SUMMARYREPORT', '0'
	EXEC SP_TEST_SOFT_BIN_YIELD_REPORT '0','0','0','0','10/01/2010','10/31/2010','DETAILREPORT', '0'
*/
ALTER PROCEDURE SP_TEST_SOFT_BIN_YIELD_REPORT
(
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_IDS	VARCHAR(100),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(100),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50),
	@REPORT_TYPE		VARCHAR(50),
	@DEFAULT_PROMPT		VARCHAR(50)
)

 AS
 BEGIN
				
	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [PROCESSSTEP ID] BIGINT,[LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),
						[Test In Date] DATETIME, [Test Out Date] DATETIME, [In Qty] INT, [Out Qty] INT,[Yield] FLOAT,[Vendor]	VARCHAR(50),[PROCESS TYPE] VARCHAR(20),
						[SOFTBIN 1] INT,[SOFTBIN 2] INT,[SOFTBIN 3] INT,[SOFTBIN 4] INT,[SOFTBIN 5] INT,[SOFTBIN 6] INT,[SOFTBIN 7] INT,[SOFTBIN 8] INT,[SOFTBIN 9] INT, [SOFTBIN 10] INT,
						[SOFTBIN 11] INT,[SOFTBIN 12] INT,[SOFTBIN 13] INT,[SOFTBIN 14] INT,[SOFTBIN 15] INT,[SOFTBIN 16] INT,[SOFTBIN 17] INT,[SOFTBIN 18] INT,[SOFTBIN 19] INT, [SOFTBIN 20] INT,
						[SOFTBIN 21] INT,[SOFTBIN 22] INT,[SOFTBIN 23] INT,[SOFTBIN 24] INT,[SOFTBIN 25] INT,[SOFTBIN 26] INT,[SOFTBIN 27] INT,[SOFTBIN 28] INT,[SOFTBIN 29] INT, [SOFTBIN 30] INT,
						[SOFTBIN 31] INT,[SOFTBIN 32] INT,[SOFTBIN 33] INT,[SOFTBIN 34] INT,[SOFTBIN 35] INT,[SOFTBIN 36] INT,[SOFTBIN 37] INT,[SOFTBIN 38] INT,[SOFTBIN 39] INT, [SOFTBIN 40] INT,
						[Tester ID] VARCHAR(50),[TEST PROGRAM]	VARCHAR(50),[Handler ID] VARCHAR(50),[Lot Type] VARCHAR(50),[PACKAGE] VARCHAR(50),
						[LC] VARCHAR(50),[INSERTION] VARCHAR(50), [LB ID] VARCHAR(50),[Temperature] VARCHAR(50),[Date Code] VARCHAR(50),
						[Loss QTY] INT,[VM QTY] INT, [OS QTY] INT ,[Dut Board No] VARCHAR(50) ,[Dut Cable No] VARCHAR(50) )

	DECLARE @SQLSTRING	NVARCHAR(4000)
	DECLARE @SQLSTRING1	NVARCHAR(4000)
	DECLARE @EXCELEXECUTESTRING	NVARCHAR(4000)
	DECLARE @CRYSTALEXECUTESTRING	NVARCHAR(4000)
		
	SET @SQLSTRING = N' INSERT INTO #result_table
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				),
				P.PART_NUMBER, PS.PROCESSSTEP_ID, LOT_NUMBER,FAB_LOT_NUMBER, ISNULL(TIB.IN_DATE, DATE_IN), ISNULL(TIB.OUT_DATE,Date_Out), ISNULL(IN_QTY, QTY_IN), ISNULL(GOOD_DICE,QTY_OUT),ROUND(Yield, 2),
				(SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID ), PS_TYPE, SOFTBIN_1,SOFTBIN_2,SOFTBIN_3,SOFTBIN_4,SOFTBIN_5,SOFTBIN_6,SOFTBIN_7,SOFTBIN_8,SOFTBIN_9,SOFTBIN_10,
				SOFTBIN_11,SOFTBIN_12,SOFTBIN_13,SOFTBIN_14,SOFTBIN_15,SOFTBIN_16,SOFTBIN_17,SOFTBIN_18,SOFTBIN_19,SOFTBIN_20,
				SOFTBIN_21,SOFTBIN_22,SOFTBIN_23,SOFTBIN_24,SOFTBIN_25,SOFTBIN_26,SOFTBIN_27,SOFTBIN_28,SOFTBIN_29,SOFTBIN_30,
				SOFTBIN_31,SOFTBIN_32,SOFTBIN_33,SOFTBIN_34,SOFTBIN_35,SOFTBIN_36,SOFTBIN_37,SOFTBIN_38,SOFTBIN_39,SOFTBIN_40,			
				ISNULL(TIB.TESTER_ID,FTL.TESTER_ID), ISNULL(TIB.Test_Pgm,FTL.FTL_Test_Program), ISNULL(TIB.Handler,FTL.Handler), lot_category, ISNULL(TIB.Package,FTL.FTL_Package_Type ), ISNULL(TIB.LC,FTL.FTL_Lead_Count),
				ISNULL(TIB.STAGE,FTL.STAGE), ISNULL(TIB.LB_ID,FTL.LB_ID), ISNULL(TIB.ROOM_TEMP,FTL.ROOM_TEMP), ISNULL(TIB.DATE_CODE,LT.LOT_DATE_CODE), ISNULL(ISNULL(TIB.LOSS_QTY,FTL.LOSS_QTY),0), ISNULL(ISNULL(TIB.VM_QTY,FTL.VM_QTY),0), ISNULL( ISNULL(TIB.OS_QTY,FTL.OS_QTY), 0 ),
				TIB.[Dut_Board_No], TIB.[Dut_Cable_No]
				FROM PRODUCT P, PROCESS_STEP PS, LOT_TABLE LT, FINAL_TEST_LOT FTL, Test_Informative_Binning TIB ,Test_Soft_Binning_Info TSB
				WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOT_ID= FTL.LOT_ID AND FTL.LOT_ID = TIB.LOT_ID AND TIB.LOT_ID = TSB.LOT_ID 
				AND lot_current_status In ( ''close'', ''HOLD'' ) '
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL )
	BEGIN
		IF ( @PROCESS_TYPE = '0' or @PROCESS_TYPE = 'all' )
		BEGIN
			SET @PROCESS_TYPE = 'FT'
		END
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END

	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') ) )'
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND ISNULL(TIB.IN_DATE, DATE_IN) >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
	END
	
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND ISNULL(TIB.OUT_DATE,Date_Out) <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
	END

	--PRINT @SQLSTRING + @SQLSTRING1

	EXEC (@SQLSTRING + @SQLSTRING1)
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
			SET @EXCELEXECUTESTRING = 'select 
			[PART NUMBER] As [DEVICE],[VENDOR],[LOT NUMBER] As [Subcon Lot No], [FAB LOT NUMBER] as [Wafer Lot No], [FAB LOT NUMBER] As [Customer Lot No],[Lot Type], [Package],
			[LC] as [L/C], [INSERTION] , [TEST PROGRAM] As [Program No], [Tester ID], [Handler ID],  [Dut Board No],[Dut Cable No],
			[LB ID] as [L/B ID],  [Temperature], [Date Code], [Test In Date], [Test Out Date],[In Qty],[Out Qty], ((([Out Qty]* 100.00)/([In Qty]-[Loss QTY]-[VM QTY]-[OS QTY]) ) /100 ) AS [Yield] ,
			[SOFTBIN 1],[SOFTBIN 2],[SOFTBIN 3],[SOFTBIN 4],[SOFTBIN 5],[SOFTBIN 6],[SOFTBIN 7],[SOFTBIN 8],[SOFTBIN 9],[SOFTBIN 10],
			[SOFTBIN 11],[SOFTBIN 12],[SOFTBIN 13],[SOFTBIN 14],[SOFTBIN 15],[SOFTBIN 16],[SOFTBIN 17],[SOFTBIN 18],[SOFTBIN 19],[SOFTBIN 20],
			[SOFTBIN 21],[SOFTBIN 22],[SOFTBIN 23],[SOFTBIN 24],[SOFTBIN 25],[SOFTBIN 26],[SOFTBIN 27],[SOFTBIN 28],[SOFTBIN 29],[SOFTBIN 30],
			[SOFTBIN 31],[SOFTBIN 32],[SOFTBIN 33],[SOFTBIN 34],[SOFTBIN 35],[SOFTBIN 36],[SOFTBIN 37],[SOFTBIN 38],[SOFTBIN 39],[SOFTBIN 40],
			[Loss QTY], [VM QTY],[OS QTY]
			from #result_table '

			IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' WHERE  [Test In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
			END
	
			IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Test Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
			END
			SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' order by [FAB LOT NUMBER], [Test In Date],[INSERTION] '

			EXEC (@EXCELEXECUTESTRING)
			
	END
	
END